﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.BasicList
{
    public partial class panel_Supplier : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;
        
        public panel_Supplier()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            LoadData();

            this.Supplier.DoubleClick += Supplier_DoubleClick;
            this.Supplier.PopupMenuShowing += Supplier_PopupMenuShowing;
            this.supplierContext.ItemClicked += supplierContext_ItemClicked;
        }

        ///////////////////////////////////////////////////
        /// Call Edit Form
        /////////////////////////////////////////////////
        private void CallEditForm()
        { 
            DataRow row = Supplier.GetFocusedDataRow();
            frm_Edit_Supplier editForm = new frm_Edit_Supplier();
            editForm.supplier_id = row[0].ToString();
            if (editForm.LoadData())
            {
                editForm.ShowDialog();
                LoadData();
            }
            else
            {
                MessageBox.Show("Cannot load data for selected record");
            } 
        }

        void Supplier_DoubleClick(object sender, EventArgs e)
        {
            CallEditForm(); 
        }

        void supplierContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.supplierContext.Hide();
            DataRow row = Supplier.GetFocusedDataRow();
            switch (e.ClickedItem.Name)
            {
                case "importSelection":
                    importDatabase();
                    break;
                case "addSelection":
                    frm_Add_Supplier add_form = new frm_Add_Supplier();
                    add_form.ShowDialog();
                    LoadData();
                    break;
                case "editSelection":
                    CallEditForm();
                    break;
                case "deleteSelection":
                    deleteRecord();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void Supplier_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.supplierContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT [222_Supplier].[SupplierID], [222_Supplier].[SupplierID_PO], [222_Supplier].[SupplierName],"
                + " [222_Supplier].[Company], [222_Supplier].[Address], [222_Country].[CountryName], [222_Supplier].[Telephone],"
                + " [222_Supplier].[Fax], [222_Supplier].[PIC], [222_Supplier].[PICSex], [222_Supplier].[PICEmail],"
                + " [222_Supplier].[PICPosition], [222_Supplier].[NameOfCommodity], [222_DeliveryTerm].[DeliveryTermName],"
                + " [222_Payment].PaymentName, [222_Supplier].[Note], [222_Supplier].[BankAccount],"
                + " [222_Supplier].[BankName], [222_Supplier].[TaxCode]"
                + " FROM [222_Supplier] LEFT OUTER JOIN"
                + " [222_Country] ON [222_Supplier].CountryID=[222_Country].CountryID LEFT OUTER JOIN"
                + " [222_DeliveryTerm] ON [222_DeliveryTerm].[DeliveryTermID]=[222_Supplier].[DeliveryTermID] LEFT OUTER JOIN"
                + " [222_Payment] ON [222_Payment].PaymentID=[222_Supplier].PaymentID";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridSupplier.DataSource = sourceDataSet.Tables[0];
                getUpdateInfo();
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("SupplierID", typeof(string));
                newTable.Columns.Add("SupplierID_PO", typeof(string));
                newTable.Columns.Add("SupplierName", typeof(string));
                newTable.Columns.Add("Company", typeof(string));
                newTable.Columns.Add("Address", typeof(string));
                newTable.Columns.Add("Country", typeof(string));
                newTable.Columns.Add("Telephone", typeof(string));
                newTable.Columns.Add("Fax", typeof(string));
                newTable.Columns.Add("PIC", typeof(string));
                newTable.Columns.Add("PICSex", typeof(string));
                newTable.Columns.Add("PICEmail", typeof(string));
                newTable.Columns.Add("PICPosition", typeof(string));
                newTable.Columns.Add("NameOfComodity", typeof(string));
                newTable.Columns.Add("DeliveryTerm", typeof(string));
                newTable.Columns.Add("Payment", typeof(string));
                newTable.Columns.Add("Note", typeof(string));
                newTable.Columns.Add("BankAccount", typeof(string));
                newTable.Columns.Add("BankName", typeof(string));
                newTable.Columns.Add("TaxCode", typeof(string));
                gridSupplier.DataSource = newTable;
            }
            Supplier.Columns[0].Caption = "Supplier ID";
            Supplier.Columns[1].Caption = "Supplier ID PO";
            Supplier.Columns[2].Caption = "Supplier Name";
            Supplier.Columns[3].Caption = "Company";
            Supplier.Columns[4].Caption = "Address";
            Supplier.Columns[5].Caption = "Country";
            Supplier.Columns[6].Caption = "Telephone";
            Supplier.Columns[7].Caption = "Fax";
            Supplier.Columns[8].Caption = "PIC";
            Supplier.Columns[9].Caption = "PIC Sex";
            Supplier.Columns[10].Caption = "PIC Email";
            Supplier.Columns[11].Caption = "PIC Position";
            Supplier.Columns[12].Caption = "Name of Comodity";
            Supplier.Columns[13].Caption = "Delivery Term";
            Supplier.Columns[14].Caption = "Payment";
            Supplier.Columns[15].Caption = "Note";
            Supplier.Columns[16].Caption = "Bank Account";
            Supplier.Columns[17].Caption = "Bank Name";
            Supplier.Columns[18].Caption = "Tax Code";            
            Supplier.BestFitColumns();
        }

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=7";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information");
            }
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }


        private void buttonEdit_Click(object sender, EventArgs e)
        {
            CallEditForm();  
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }

        ////////////////////////////////////
        /// Update database From AS400
        ////////////////////////////////////

        private void importDatabase()
        {
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();
            
            bool have_data = false;

            importFile.Filter = "Excel 97-2003 (.xls)|*.xls|Excel 2007-2010 (.xlsx)|*.xlsx";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("Select [SupplierID], [SupplierID_PO], [SupplierName], [Company], [Address]"
                        + " From [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);
                    have_data = true;
                    excelConnection.Close();
                }
                catch
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    MessageBox.Show("Cannot import data from file. Please check your file");
                }
            }            

            if (have_data)
            {

                DataSet supplier = new DataSet();
                string sql = null;
                SqlDataAdapter adapter;
                DataTable supplier_local;
                try
                {
                    //Get Data From Local Database
                    myConnection.Open();
                    sql = "SELECT * FROM [222_Supplier]";
                    SqlCommand cmd = new SqlCommand(sql, myConnection);
                    adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    adapter.Fill(supplier, "222_Supplier");
                    supplier_local = supplier.Tables["222_Supplier"];
                    
                    foreach (DataRow excel_row in excel_data.Rows)
                    {
                        DataRow[] current_row = supplier_local.Select("SupplierID='" + excel_row["SupplierID"].ToString() + "'");
                        if (current_row.Length > 0)
                        {
                            foreach (DataRow local_row in current_row)
                            {
                                local_row.BeginEdit();
                                local_row["SupplierID_PO"] = excel_row["SupplierID_PO"].ToString();
                                local_row["SupplierName"] = excel_row["SupplierName"].ToString();
                                local_row["Company"] = excel_row["Company"].ToString();
                                local_row["Address"] = excel_row["Address"].ToString();
                                local_row.EndEdit();
                            }
                        }
                        else
                        {
                            DataRow new_row = supplier_local.NewRow();
                            new_row["SupplierID"] = excel_row["SupplierID"].ToString();
                            new_row["SupplierID_PO"] = excel_row["SupplierID_PO"].ToString();
                            new_row["SupplierName"] = excel_row["SupplierName"].ToString();
                            new_row["Company"] = excel_row["Company"].ToString();
                            new_row["Address"] = excel_row["Address"].ToString();
                            supplier_local.Rows.Add(new_row);
                        }
                    } 


                    SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                    //Update to Local Database
                    adapter.Update(supplier, "222_Supplier");

                    adapter.Dispose();
                    cmd.Dispose();
                    myConnection.Close();

                    //Update History
                    myConnection.Open();
                    sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=7";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();

                    LoadData();

                    MessageBox.Show("Database has been updated successfull.");

                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot connect to AS400");
                }
            }
        }

        private void deleteRecord()
        {
            if (MessageBox.Show("Are you really to delete selected record now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                DataRow row = Supplier.GetFocusedDataRow();
                try
                {
                    myConnection.Open();
                    string sql = "DELETE [222_Supplier] WHERE SupplierID='" + row[0].ToString() + "'";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();
                    MessageBox.Show("Supplier deleted success.");
                    LoadData();
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show(ex.Message);
                    MessageBox.Show("Cannot delete Supplier. Please try again.");
                }
            }
        }

        private void deleteButton_Click(object sender, EventArgs e)
        {
            deleteRecord();
        }

        private void addButton_Click(object sender, EventArgs e)
        {
            frm_Add_Supplier add_form = new frm_Add_Supplier();
            add_form.ShowDialog();
            LoadData();
        }
    }
}
